package com.hbnu.dao;
import com.hbnu.entity.FileStat;
import com.hbnu.entity.ShareStat;
import com.hbnu.entity.UserFile;
import com.hbnu.tools.UserFileTool;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;


public class FunctionDao extends JDBC{

    //创建文件夹
    public int createFolder(String userId, int pid, String name) {
        try(PreparedStatement preparedStatement=getPreparedStatement0(
                "insert into files_folders(user_id,pid,fname,created_time) values(?,?,?,now());"
        )){
            // 设置 PreparedStatement 参数
            preparedStatement.setString(1,userId);
            preparedStatement.setInt(2,pid);
            preparedStatement.setString(3,name);
            // 执行插入操作
            if (preparedStatement.executeUpdate() > 0) {
                // 获取生成的主键
                try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        // 获取生成的主键值（假设是 int 类型）
                        return generatedKeys.getInt(1);
                    }
                }
            }else return  -1;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return -1;
    }

    //获取文件
    public FileStat getFile(int fid) {
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "select f.fname,f.created_time,sf.file_size,sf.file_md5,sf.file_path from files sf\n" +
                        "join (select file_id,fname,created_time from files_folders where fid= ? and is_deleted=0) f\n" +
                        "on sf.file_id = f.file_id;"
        )){
            preparedStatement.setInt(1,fid);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                FileStat fileStat=new FileStat();
                fileStat.setName(resultSet.getString(1));
                fileStat.setCreated_time(resultSet.getTimestamp(2).toLocalDateTime());
                fileStat.setSize(resultSet.getLong(3));
                fileStat.setMd5(resultSet.getString(4));
                fileStat.setPath(resultSet.getString(5));
                return fileStat;
            }else return null;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //修改名称
    public boolean updateFname(int id, String newName) {
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "update files_folders set fname= ? where fid=?;"
        )){
            preparedStatement.setString(1,newName);
            preparedStatement.setInt(2,id);
           return preparedStatement.executeUpdate()>0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    //修改父文件
    public boolean updatePid(int fid, int nFid){
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "update files_folders set pid = ? where fid= ?;"
        )){
            preparedStatement.setInt(1,nFid);
            preparedStatement.setInt(2,fid);
            return preparedStatement.executeUpdate()>0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    //删除文件
    public boolean deleteF(int id) {
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "update files_folders set is_deleted = 1 where fid=?;"
        )){
            preparedStatement.setInt(1,id);
            return preparedStatement.executeUpdate()>0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    //删除文件夹,调用存储过程
    //call update_folder_delete_status(?);
    public boolean deleteFolder(int id) {
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "call update_folder_delete_status(?,1);"
        )){
            preparedStatement.setInt(1,id);
            return preparedStatement.executeUpdate()>0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    //保存服务器的数据到数据库
    public int insertFileData(String uid,long size,String md5,String path) {
        try(PreparedStatement preparedStatement=getPreparedStatement0(
                "insert into files(user_id,file_size,file_md5,file_path,created_time) \n" +
                        "values(?,?,?,?,now())"
        )){
            // 设置 PreparedStatement 参数
            preparedStatement.setString(1, uid);
            preparedStatement.setLong(2, size);
            preparedStatement.setString(3, md5);
            preparedStatement.setString(4, path);
            // 执行插入操作
            if (preparedStatement.executeUpdate() > 0) {
                // 获取生成的主键
                try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        // 获取生成的主键值（假设是 int 类型）
                        return generatedKeys.getInt(1);
                    }
                }
            }else return  -1;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return -1;
    }

    //保存到用户数据库
    public int saveUserFile(String uid,String fname,int pid,int fileId) {
        try(PreparedStatement preparedStatement=getPreparedStatement0(
                " insert into files_folders(user_id,fname,pid,created_time,file_id,is_file)\n" +
                        "values(?,?,?,now(),?,1)"
        )){
            // 设置 PreparedStatement 参数
            preparedStatement.setString(1, uid);
            preparedStatement.setString(2, fname);
            preparedStatement.setInt(3, pid);
            preparedStatement.setInt(4, fileId);
            if (preparedStatement.executeUpdate() > 0) {
                // 获取生成的主键
                try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        // 获取生成的主键值（假设是 int 类型）
                        return generatedKeys.getInt(1);
                    }
                }
            }else return  -1;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return -1;
    }

    //检查md5是否存在
    public int checkMd5(String md5) {
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "select file_id from files where file_md5= ?;"
        )){
            // 设置 PreparedStatement 参数
            preparedStatement.setString(1, md5);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next())
            return  resultSet.getInt(1);
            else return -1;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //创建分享
    public boolean createShare(String uid, ShareStat shareStat, int time) {
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "insert into shares(uid,fid,uuid,pwd,time,size,collect)\n" +
                        "values(?,?,?,?,date_add(now(), interval ? hour),?,?);"
        )){
            // 设置 PreparedStatement 参数
            preparedStatement.setString(1, uid);
            preparedStatement.setInt(2, shareStat.getFid());
            preparedStatement.setString(3, shareStat.getUuid());
            preparedStatement.setString(4, shareStat.getPwd());
            preparedStatement.setInt(5, time);
            preparedStatement.setLong(6, shareStat.getSize());
            preparedStatement.setString(7, shareStat.getText());
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //获得分享id
    public ShareStat getShareFid(String code, String pwd) {
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "select uid,fid,size,collect from shares where uuid = ? and pwd= ? and now()<time and is_deleted = 0;"
        )){
            preparedStatement.setString(1,code);
            preparedStatement.setString(2,pwd);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next())
                return new ShareStat(resultSet.getString(1),resultSet.getInt(2),resultSet.getLong(3),resultSet.getString(4));
            else return null;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    //获得分享文件、夹  || 通过fid进行查询，并保存到本地缓存


    //获得分享文件、夹  || 通过fid进行查询，并保存到本地缓存
    public Map<Integer, UserFile> getShareF(String text) {
        String sql="select fid,pid,fname,file_id,is_file\n" +
                "from files_folders where fid in ("+text+");";
        try(Statement statement = getStatement()){
            ResultSet resultSet = statement.executeQuery(sql);
           return UserFileTool.initUserFileMap(resultSet);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        //call select_share(4);
    }


    //不允许自己保存自己链接 bug
    public boolean saveShare0(String uid,int fid, Map<Integer, UserFile> tmpIdUserFileMap) {
        try(Statement statement = getStatement()){
            connection.setAutoCommit(false);  // 开始事务管理
            Queue<Integer> userFileQueue=new LinkedList<>();
            userFileQueue.add(fid);
            while (!userFileQueue.isEmpty()){
                UserFile userFile= tmpIdUserFileMap.get(userFileQueue.poll());
                Integer file_id=userFile.getFile_id()==0?null:userFile.getFile_id();
               String sql = String.format(
                        "insert into files_folders(user_id,pid,fname,created_time,file_id,is_file) \n" +
                        "values('%s',%d,'%s',now(),%d,%d);",
                       uid,userFile.getPid(),userFile.getFname(),file_id,userFile.is_file()?1:0);
                statement.addBatch(sql);
                if(userFile.hasChildren()){
                    userFileQueue.addAll(userFile.getList());
                }
            }
            int[] updateCounts = statement.executeBatch();
            for (int count : updateCounts) {
                // 检查每条 SQL 是否成功执行
                if (count == Statement.EXECUTE_FAILED) {
                    connection.rollback();  // 如果某条语句失败，回滚事务
                    return false;  // 提前返回，确保不会提交事务
                }
            }
            // 提交事务
            connection.commit();
            return true;

        } catch (SQLException e) {
            try {
                connection.rollback();  // 如果有错误，回滚事务
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        }finally {
            try {
                //恢复自动提交
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public Map<Integer,Integer> saveShare(String uid,int fid, Map<Integer, UserFile> tmpIdUserFileMap) {
        try(PreparedStatement preparedStatement = getPreparedStatement0(
                "insert into files_folders(user_id,pid,fname,created_time,file_id,is_file) \n" +
                "values(?,?,?,now(),?,?);"
        )){
            connection.setAutoCommit(false);  // 开始事务管理
            Queue<Integer> userFileQueue=new LinkedList<>();
            Map<Integer,Integer> keyMap=new HashMap<>();
            userFileQueue.add(fid);
            int key=0;
            while (!userFileQueue.isEmpty()){
                UserFile userFile= tmpIdUserFileMap.get(userFileQueue.poll());
                int file_id=userFile.getFile_id();
                preparedStatement.setString(1,uid);
                int pid = userFile.getPid();
                if(keyMap.containsKey(pid)) pid=keyMap.get(pid);
                preparedStatement.setInt(2,pid);
                preparedStatement.setString(3,userFile.getFname());
                if (file_id==0) {
                    preparedStatement.setNull(4, java.sql.Types.INTEGER); // 设置为 NULL，使用 SQL INTEGER 类型
                } else {
                    preparedStatement.setInt(4, file_id); // 如果 file_id 不为 NULL，使用 setInt
                }
                preparedStatement.setInt(5,userFile.is_file()?1:0);
                //获取key
                if (preparedStatement.executeUpdate() > 0) {
                    // 获取生成的主键
                    try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
                        if (generatedKeys.next()) {
                            // 获取生成的主键值（假设是 int 类型）
                            key= generatedKeys.getInt(1);
                        }
                    }
                }else throw new RuntimeException("数据库异常") ;
                //将子内容添加至队列
                if(userFile.hasChildren()){
                    userFileQueue.addAll(userFile.getList());
                }
                keyMap.put(userFile.getFid(),key);
                //便于缓存修改
                userFile.setFid(key);
                userFile.setPid(pid);
            }
            // 提交事务
            connection.commit();
            return keyMap;

        } catch (SQLException e) {
            try {
                connection.rollback();  // 如果有错误，回滚事务
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        }finally {
            try {
                //恢复自动提交
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    //计算文件大小
    public long sumFileSize(List<Integer> fileIdList) {
        try(Statement statement = getStatement()){
            String sql=
                    "select sum(file_size) " +
                    "from files " +
                    "where file_id in (";
                String ids = fileIdList.stream()
                        .map(String::valueOf) // 将 Integer 转为 String
                        .collect(Collectors.joining(","));
            ResultSet resultSet = statement.executeQuery(sql+ids+");");
            if (resultSet.next()){
                return resultSet.getLong(1);
            }
            return -1;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //修改存储大小
    public void updateAddSize(String uid, long size) {
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "update users set size = size+ ? where user_id = ?;"
        )){
            preparedStatement.setLong(1,size);
            preparedStatement.setString(2,uid);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
    public void updateReduceSize(String uid, long size) {
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "update users set size = size - ? where user_id = ?;"
        )){
            preparedStatement.setLong(1,size);
            preparedStatement.setString(2,uid);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }


    //获得全部有效链接，分享链接
    public List<ShareStat> getShareList(String uid) {
        try (PreparedStatement preparedStatement = getPreparedStatement(
                "select sid,fid,uuid,pwd,time,size from shares where uid = ? and is_deleted = 0"
        )){
            preparedStatement.setString(1,uid);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<ShareStat> shareStatList = new ArrayList<>();
            while (resultSet.next()) {
                ShareStat shareStat=new ShareStat(
                        resultSet.getInt(1),
                        resultSet.getInt(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getTimestamp(5).toLocalDateTime(),
                        resultSet.getLong(6)
                );
                shareStatList.add(shareStat);
            }
            return shareStatList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //取消分享
    public boolean shareClose(int id) {
        try(PreparedStatement preparedStatement=getPreparedStatement(
                "update shares set is_deleted = 1 where sid= ?;"
        )){
            preparedStatement.setInt(1,id);
            return preparedStatement.executeUpdate()>0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
